﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
using CommClass;

namespace DMS
{
    public partial class FrmDoImportSAP : DMS.FrmTemplate
    {
        DataConnection dc = new DataConnection();
        DataSource dsExcelFile;
        MO mo = new MO(App.Ds);
        public FrmDoImportSAP()
        {
            InitializeComponent();
        }

        private void FrmDoImportSAP_Load(object sender, EventArgs e)
        {

        }
       
       
        //检查EXCEL数据是否符合导入要求
        public string checkExcelData(DataTable dt)
        {
            string donumber = "";
           // DataRow[] dr;
              int carrierid;
              int prodid;
              int areaid;
           
              for (int j = 0; j < dt.Rows.Count; j++)
              {
                  string sss = dt.Rows[j][24].ToString();
                  string sssa = dt.Rows[j][7].ToString();
                  string sssaa = dt.Rows[j][23].ToString();
                  //获取承运商编码
                  carrierid =mo.CheckCarrierID (dt.Rows[j][24].ToString());
                  //获取产品编码
                  prodid =mo.CheckProdID(dt.Rows[j][7].ToString());
                  //获取区域编码
                  areaid =mo.checkAreaid(dt.Rows[j][23].ToString());
                  //dr = dt.Select("物料代码='" + dt.Rows[j][9].ToString() + "' and 批次='"+dt.Rows [j][21].ToString ()+"'");
                 // DataTable dpt = new DataTable();
                  //dpt = dr.CopyToDataTable();



                  //承运商编号和产品编号都能匹配到
                  if (carrierid > 0 && prodid> 0 && areaid> 0)
                  {
                      //判断源数据提单和产品是否重复
                      // if (dpt.Rows.Count > 1)
                      // {
                      //     listBox1.Items.Add(dt.Rows[j][1] + "该提单源数据重复被忽略。");
                     //      donumber = dt.Rows[0][1].ToString();
                     //      break;
                     //  }
                     //  else
                     //  {
                       
                           //判断提单是否已存在
                           if (mo.checkDoNumber (dt.Rows[0][1].ToString()))
                           {

                               listBox1.Items.Add(dt.Rows[0][1] + "该提单系统中已存在被忽略。");
                               donumber = dt.Rows[0][1].ToString();
                               break;

                        //   }

                       }
                    
                  }
                  else
                  {
                      listBox1.Items.Add("提单:" + dt.Rows[j][1] + ",承运商:" + dt.Rows[j][24] + ",产品代码:" + dt.Rows[j][7] + ",区域：" + dt.Rows[j][23] + "与系统不符，请核实。");
                      donumber = dt.Rows[0][1].ToString();
                      continue;

                  }

              }
            
            return donumber;

        }



        private void button1_Click_1(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();

            dlg.Filter = "Excel文件|*.xls;*.xlsx|所有文件|*.*";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                this.tbFileName.Text = dlg.FileName;
                dlg.Dispose();
                dc.DBName = tbFileName.Text;
                dsExcelFile = new ExcelDataSource(dc);
                cbbSheets.DataSource = dsExcelFile.GetTableNames();
                cbbSheets.SelectedIndex = 0;
            }


        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            if (tbFileName.Text != null && tbFileName.Text.Trim() != "")
            {
                string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';Data Source=" + tbFileName.Text;
                OleDbConnection myOleDbConnection = new OleDbConnection(constr);
                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter("select * from [" + cbbSheets.Text + "] order by 提单号 ", myOleDbConnection);
                DataTable myDataTable = new DataTable();
                myOleDbDataAdapter.Fill(myDataTable);



                //显示excel数据
                dvgDO.DataSource = myDataTable;
                //dsExcelFile.GetRecord("select * from [" + cbbSheets.Text + "] order by 提单号 ").Tables[0];

                this.button3.Enabled = true;
            }
            else
            {
                  MessageBox .Show ( "请选择文件！");
            }
        }

        private void button3_Click_1(object sender, EventArgs e)
        {
            tabControl1.SelectedIndex = 1;
            listBox1.Items.Add("开始导入数据...");
            ////获取excel数据
            DataTable dtt =(DataTable)dvgDO.DataSource;
            
            DeliveryOrder d = new DeliveryOrder();
            DOEntry de = new DOEntry();
            string deliverynumber;

            //遍历excel数据并上传至数据库
            int i = 0;
            int carrierid;
            int areaid;
           
            while (i <dtt.Rows .Count  )
            {
                DataRow[] dr;
                DataTable dt;
                dr =dtt.Select("提单号='" + dtt.Rows[i][1].ToString() + "'");
                dt = dr.CopyToDataTable();
                deliverynumber = checkExcelData(dt);
                if (deliverynumber == "")
                {
                    if (dt.Rows[0][32] != null && dt.Rows[0][32].ToString().Trim() != "")
                    {
                        d.Stock = dt.Rows[0][32].ToString().Trim();
                    }
                    else
                    {
                        d.Stock = "迎胜御桥仓";
                    }
                   
                    carrierid = mo.CheckCarrierID(dt.Rows[0][24].ToString());
                    d.CarrierID = carrierid;
                    areaid = mo.checkAreaid(dt.Rows[0][23].ToString());
                    d.AreaID = areaid;
                    d.DeliveryNumber = dt.Rows[0][1].ToString();
                    d.OrderNumber = dt.Rows[0][2].ToString();
                    d.OrderType = dt.Rows[0][4].ToString();
                    //提货方式
                    d.Tihuofangshi = dt.Rows[0][5].ToString();
                    d.OrderDatetime = DateTime.Parse(dt.Rows[0][6].ToString());
                    d.CustName = dt.Rows[0][10].ToString();
                    d.DeliveryTo = dt.Rows[0][12].ToString();
                    d.DeliveryAddress = dt.Rows[0][13].ToString();
                    d.Remark = dt.Rows[0][16].ToString();
                    //联系方式  
                    d.Lianxifangshi = dt.Rows[0][17].ToString();
                    //联系电话  
                    d.Lianxidianhua = dt.Rows[0][18].ToString();
                    d.SaleType = dt.Rows[0][19].ToString();
                    //发票 
                    d.Fapiao = dt.Rows[0][20].ToString();
                    //下单次数
                    d.Xiadancishu = dt.Rows[0][25].ToString();
                    //支付信息 
                    d.Zhifuxinxi = dt.Rows[0][26].ToString();
                    //优惠数目
                    double ddd = 0;
                    Double.TryParse(dt.Rows[0][27].ToString(), out ddd);
                    d.Youhui = ddd;
                    //维护人
                    d.BusinessMan = dt.Rows[0][28].ToString();
 
                    if (mo.SaveDoHeard(d))
                    {
                        
                        for (int j = 0; j < dt.Rows.Count; j++)
                        {
                            de.ProdID = mo.CheckProdID(dt.Rows[j][7].ToString());
                            de.OrderQty = double.Parse(dt.Rows[j][14].ToString());
                            DataTable dtProduct = App.GetProductByCode(dt.Rows[j][7].ToString());
                            if (dtProduct.Rows.Count > 0)
                            {
                                de.Weight = double.Parse(dtProduct.Rows[0]["Weight"].ToString()) * de.OrderQty;
                            }
                            de.Batch = "";
                            //单价
                            de.Price= double.Parse(dt.Rows[j][21].ToString());
                            //金额
                            de.Amount=double.Parse (dt.Rows[j][22].ToString());

                            if (!mo.SaveDoEntry(de, d))
                            {
                                
                                mo.DeleteDo(d.DeliveryNumber);
                                listBox1.Items.Add("提单："+d.DeliveryNumber +"导入失败！");
                                break;
                            }
                           
                           
                        }
                    }
                  
                }
                i = i + dt.Rows.Count;//跳到下一个需要导入的提单
             }

                listBox1.Items.Add("数据导入完成！");

         }

        private void button4_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void cbbSheets_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void tbFileName_TextChanged(object sender, EventArgs e)
        {

        }
    }
    
}

